package com.flute.tools.data.mysql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.commons.lang.StringUtils;

import com.flute.tools.data.DataException;
import com.flute.tools.data.DataIterator;
import com.flute.tools.data.DataRecord;
import com.flute.tools.data.IEditor;
import com.flute.tools.util.ExceptionUtil;

public class NewMysqlHandler {
	private static final String PROXOOL_DB_ALIAS = "proxool.mysql-flute";
	private Connection connection;
	private ConnectionInformation ci;

	private static ConcurrentHashMap<String, String[]> keyCache = new ConcurrentHashMap<String, String[]>();

	public NewMysqlHandler(ConnectionInformation ci) {
		this.ci = ci;
		this.connection = ConnectionHandler.getConnection(PROXOOL_DB_ALIAS);
	}

	public NewMysqlHandler() {
		this.connection = ConnectionHandler.getConnection(PROXOOL_DB_ALIAS);
	}

	public Connection getConnection() {
		return connection;
	}

	public static void main(String[] args) {
		new NewMysqlHandler();
	}

	public void execute(String sql) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql, ResultSet.CONCUR_UPDATABLE);
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new DataException("Failed to execute sql '" + sql + "'"
					+ ExceptionUtil.getExceptionContent(e));
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
				}
			}
		}

	}

	public void commit() throws SQLException {
		if (connection != null) {
			connection.commit();
		}
	}

	public void setAutoCommit(boolean autoCommit) throws SQLException {
		if (connection != null) {
			connection.setAutoCommit(autoCommit);
		}
	}

	/**
	 * 按指定模式，获取数据库数据
	 * 
	 * @param con
	 *            数据库连接
	 * @param sql
	 *            sql语句
	 * @return 数据库数据
	 */
	public DataIterator<DataRecord> iterator(final String sql) {
		return new DataIterator<DataRecord>() {

			private ResultSet resultSet;
			private Statement ps;
			private ResultSetMetaData meta;

			{
				initial();
			}

			private void initial() {
				try {
					ps = connection.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
					// ps.setMaxRows(1000);

					resultSet = ps.executeQuery(sql);
					resultSet.setFetchSize(100);
					meta = resultSet.getMetaData();
				} catch (Exception e) {
					close();
					throw new DataException("Failed to initial sql '" + sql
							+ "'\n" + ExceptionUtil.getExceptionContent(e));
				}
			}

			@Override
			public boolean hasNext() {
				try {
					boolean has = resultSet.next();
					resultSet.previous();
					return has;
				} catch (Exception e) {
					throw new DataException(
							"Failed to get whether there is next record.\n"
									+ ExceptionUtil.getExceptionContent(e));
				}
			}

			@Override
			public DataRecord next() {
				try {
					resultSet.next();
					DataRecord dr = new DataRecord();
					for (int i = 1; i <= meta.getColumnCount(); i++) {
						dr.AddField(meta.getColumnLabel(i), String
								.valueOf(resultSet.getObject(meta
										.getColumnLabel(i))));
					}
					return dr;
				} catch (Exception e) {
					throw new DataException("Failed to get next record.\n"
							+ ExceptionUtil.getExceptionContent(e));
				}
			}

			@Override
			public void close() {
				if (ps != null) {
					try {
						ps.close();
					} catch (SQLException e) {
					}
				}
				if (resultSet != null) {
					try {
						resultSet.close();
					} catch (SQLException e) {
					}
				}
			}

			@Override
			public void reset() {
				close();
				initial();
			}
		};
	}

	/**
	 * 获得数据库操作接口
	 * 
	 * @param connection
	 *            数据库连接
	 * @return 数据库操作接口
	 */
	public IEditor editor(final String table) {
		return new IEditor() {

			@Override
			public void begineTransaction() {
				try {
					connection.setAutoCommit(false);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			@Override
			public void commit() {
				try {
					connection.commit();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			@Override
			public void updateRecord(DataRecord dr) {
				if (dr != null && dr.getAllFields().length > 0) {
					String sql = "update  " + table + getSetString(dr, table)
							+ getWhereString(dr, table);

					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement(sql,
								ResultSet.CONCUR_UPDATABLE);
						ps.executeUpdate();
					} catch (SQLException e) {
						throw new DataException("Failed to update record '"
								+ sql + "'"
								+ ExceptionUtil.getExceptionContent(e));
					} finally {
						if (ps != null) {
							try {
								ps.close();
							} catch (SQLException e) {
							}
						}
					}
				}
			}

			@Override
			public void addRecord(DataRecord dr, boolean override) {
				if (containsRecord(dr) && override) {
					deleteRecord(dr);
				}
				addRecord(dr);
			}

			@Override
			public void addRecord(DataRecord dr) {
				if (dr.getAllFields().length > 0) {
					String sql = getInsertString(dr, table);

					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement(sql,
								ResultSet.CONCUR_UPDATABLE);
						ps.execute(sql);
					} catch (SQLException e) {
						throw new DataException("Failed to add record '" + sql
								+ "'" + ExceptionUtil.getExceptionContent(e));
					} finally {
						if (ps != null) {
							try {
								ps.close();
							} catch (SQLException e) {
							}
						}
					}
				}
			}

			@Override
			public void deleteRecord(DataRecord dr) {
				if (dr != null && dr.getAllFields().length > 0) {
					String sql = "delete from " + table
							+ getWhereString(dr, table);

					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement(sql,
								ResultSet.CONCUR_UPDATABLE);
						ps.execute(sql);
						ps.close();
					} catch (SQLException e) {
						throw new DataException("Failed to delete record '"
								+ sql + "'"
								+ ExceptionUtil.getExceptionContent(e));
					} finally {
						if (ps != null) {
							try {
								ps.close();
							} catch (SQLException e) {
							}
						}
					}
				}
			}

			@Override
			public void processRecord(DataRecord dr) {
				switch (dr.getDataAction()) {
				case Add:
					addRecord(dr);
					break;
				case Delete:
					deleteRecord(dr);
					break;
				case Update:
					updateRecord(dr);
					break;
				default:
					break;
				}
			}

			@Override
			public boolean containsRecord(DataRecord dr) {
				String sql = "select * from  " + table
						+ getWhereString(dr, table);
				DataIterator<DataRecord> it = iterator(sql);
				boolean contains = false;
				if (it.hasNext()) {
					contains = true;
				}
				it.close();

				return contains;
			}
		};
	}

	private String getValidValue(String value) {
		if (value == null) {
			return "";
		}
		return value.contains("\\'") ? value.replace("\\'", "'").replace("'",
				"''").replace("\\", "") : value.replace("'", "''").replace(
				"\\", "");
	}

	private String getInsertString(DataRecord dr, String table) {
		if (dr == null || StringUtils.isBlank(table)
				|| dr.getAllFields().length == 0) {
			return "";
		}
		StringBuffer fields = new StringBuffer();
		for (String s : dr.getAllFields()) {
			fields.append(s + ",");
		}
		fields.deleteCharAt(fields.length() - 1);

		StringBuffer values = new StringBuffer();
		for (String s : dr.getAllFields()) {
			values.append("'" + getValidValue(dr.getFieldValue(s)) + "',");
		}
		values.deleteCharAt(values.length() - 1);

		return "insert ignore into " + table + "(" + fields.toString()
				+ ") values(" + values.toString() + ")";
	}

	private String getWhereString(DataRecord dr, String table) {
		String[] ks = getPrimaryKeyMetaData(table);
		List<String> list = new ArrayList<String>();
		for (String s : ks) {
			list.add(s + "='" + getValidValue(dr.getFieldValue(s)) + "'");
		}

		if (list.isEmpty()) {
			return "";
		}

		String values = StringUtils.join(list, " and ");

		return " where " + values + " ";
	}

	private String getSetString(DataRecord dr, String table) {
		StringBuffer values = new StringBuffer();
		for (String s : dr.getAllFields()) {
			values.append(s + "='" + getValidValue(dr.getFieldValue(s)) + "',");
		}

		if (values.length() == 0) {
			return "";
		}

		return " set " + values.toString().substring(0, values.length() - 1)
				+ " ";
	}

	private String[] getPrimaryKeyMetaData(String tableName) {
		if (keyCache.containsKey(ci.getDbName() + tableName)) {
			return keyCache.get(ci.getDbName() + tableName);
		} else {
			ArrayList<String> list = new ArrayList<String>();
			DatabaseMetaData meta;
			try {
				meta = connection.getMetaData();

				ResultSet rs = meta.getPrimaryKeys(null, null, tableName);
				while (rs.next()) {
					// field name
					list.add(rs.getString(4));
				}
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

			keyCache.put(ci.getDbName() + tableName, list
					.toArray(new String[] {}));
			return list.toArray(new String[] {});
		}
	}

	public boolean containsRecord(String table, DataRecord dr)
			throws SQLException {
		String sql = "select * from  " + table + getWhereString(dr, table);
		DataIterator<DataRecord> it = iterator(sql);
		boolean contains = false;
		try {
			if (it.hasNext()) {
				contains = true;
			}
		} finally {
			it.close();
		}

		return contains;
	}

	public DataRecord getRecordByKey(String table, DataRecord dr) {
		String sql = "select * from  " + table + getWhereString(dr, table);
		DataIterator<DataRecord> it = iterator(sql);
		DataRecord result = null;
		try {
			if (it.hasNext()) {
				result = it.next();
			}
		} finally {
			it.close();
		}

		return result;
	}

	public void close() {
		ConnectionHandler.releaseConnection(connection);
	}

}
